﻿
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.IO;
using System.Windows.Forms;
using System.Xml;
using Microsoft.Office.Tools.Excel;
using Excel = Microsoft.Office.Interop.Excel;
using ConfigLibrary;
using System.Runtime.Serialization.Formatters.Binary;
using System.Windows;
using System.Runtime.InteropServices;
using System.Threading;



namespace TridentReport
{
  public partial class tridentreport : Form
  {
    DataTable tridenttable = new DataTable();
    DataRow row;
    MySqlConn connect = new MySqlConn();
    public static bldgportal bldgportalpage;
    Excel.Application xlst;
    Excel.Application xlsr;
    Excel.Workbook wbt;
    public static Excel.Worksheet wstc;
    public static Excel.Worksheet wstp;
    Excel.Workbook wbr;
    public static Excel.Worksheet wsrc;
    public static Excel.Worksheet wsrp;
    string tcell;
    public static string str;
    public static string seq;
    public static string strseq;
    public static int open;
    int rowcount;
    int columncount;
    DateTime go;
    DateTime complete;
    DateTime today = DateTime.Today;
    
    
    bool active;
    bool openedds = false;
    string status;
    int lastrowt;
    int lastcolt;
    int lastrowr;
    bool loggedin = false;
    private string _pass;
    DataTable newstores;
    int tr;

    public tridentreport()
    {
      InitializeComponent();
      connectdatabase();
      trident_load();
      disconnect();
      opentrident();
      openremoved();
      tridentheaders();
      removedheaders();
      //fillobservations();
      fillreports();
      formatwstc();
      formatwsrc();
      printerwstc();
      printwsrc();
      compare_wstc_to_wstp();
      compare_wsrc_to_wsrp();
      pastdue();
      message();
      shutdown();
      Dispose();
      Environment.Exit(0);
    }

    public void connectdatabase()
    {
      connect.Connect(_pass);
    }

    public void trident_load()
    {
      connect.loadtrident();
    }

    public void disconnect()
    {
      connect.Disconnect();
    }

    private void opentrident()
    {
      xlst = new Excel.Application();
      xlst.Visible = true;
      xlst.DisplayAlerts = false;
      string path = Directory.GetCurrentDirectory();
      #if (DEBUG)
      {
        wbt = xlst.Workbooks.Open("\\\\c:\\Reports\\Trident\\Trident.xls", 0, false, 5, "", "", true, Type.Missing, "", true, false, 0, true, false, false);
      }
      #elif! (DEBUG)
      {
        wbt = xlst.Workbooks.Open("\\\\u:\\Reports\\Trident\\Trident.xls", 0, false, 5, "", "", true, Type.Missing, "", true, false, 0, true, false, false);
      }
      #endif
      wstc = wbt.Worksheets.Add(Type.Missing, wbt.Worksheets[wbt.Worksheets.Count], Type.Missing, Type.Missing);
      wstp = wbt.Worksheets[wbt.Worksheets.Count - 1];
      string wsn = String.Format("{0:MM.dd.yy}", MySqlConn.dt);
      wstc.Name = wsn;
      xlst.DisplayAlerts = true;
    }

    private void openremoved()
    {
      xlsr = new Excel.Application();
      xlsr.Visible = true;
      xlsr.DisplayAlerts = false;
      #if (DEBUG)
      {
        wbr = xlsr.Workbooks.Open("\\\\c:\\Reports\\Trident\\Removed_Stores.xls", 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
      }
      #elif! (DEBUG)
      {
        wbr = xlsr.Workbooks.Open("\\\\u:\\Reports\\Trident\\Removed_Stores.xls", 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
      }
      #endif
      wsrc = wbr.Worksheets.Add(Type.Missing, wbr.Worksheets[wbr.Worksheets.Count], Type.Missing, Type.Missing);
      wsrp = wbr.Worksheets[wbr.Worksheets.Count - 1];
      string wsn = String.Format("{0:MM.dd.yy}", MySqlConn.dt);
      wsrc.Name = wsn;
      xlsr.DisplayAlerts = true;
    }

    private void tridentheaders()
    {
      wstp.Range["A1:AS1"].Copy();
      wstc.Range["A1"].PasteSpecial(Excel.XlPasteType.xlPasteAll, Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, Type.Missing, Type.Missing);
    }

    private void removedheaders()
    {
      wsrp.Range["A1:AS1"].Copy();
      wsrc.Range["A1"].PasteSpecial(Excel.XlPasteType.xlPasteAll, Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, Type.Missing, Type.Missing);
    }

    private void fillobservations()
    {
      bldgportalpage = new bldgportal(tridenttable, tr);
      bldgportalpage.ShowDialog();
    }

    private void fillreports()
    {
      tridenttable = MySqlConn.trident.Tables["trident"];
      rowcount = tridenttable.Rows.Count;
      columncount = tridenttable.Columns.Count;
      int year = today.Year;

      for (int tr = 0; tr < rowcount; tr++)
      {
        lastrowt = wstc.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Row;
        lastrowr = wsrc.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Row;
        int pyp;
        row = tridenttable.Rows[tr];
        int xlc = 0;
        string test1 = row["prog_yr_prj"].ToString();
        string[] number = { "0", "1", "2", "3", "4", "5", "6", "7", "8", "9" };
        if (number.Any(s => test1.Contains(s)))
        {
          pyp = Convert.ToInt32(row["prog_yr_prj"]);
        }
        else
        {
          pyp = 0;
        }

        status = Convert.ToString(row["proj_status"]);
        //if (row["open"] is DBNull)
        //{
        //    open = 0;
        //}
        //else
        //{
        //    open = Convert.ToInt32(row["open"]); ;
        //}
        string city = row["city"].ToString();

        if (status == "Active")
        {
          string test2 = row["go_prj"].ToString();
          
          if (number.Any(s => test2.Contains(s)))
          {
            go = Convert.ToDateTime(row["go_prj"]);
          }
        }
        str = row["str_num"].ToString();
        seq = (string)row["str_seq"];

        int sq = Convert.ToInt32(seq);
        if (sq >= 0 && sq <= 9)
        {
          seq = "0" + Convert.ToString(sq);
        }
        strseq = str + "-" + seq;
        if (row["go_prj"].ToString() != " ")
        {
          complete = go.AddDays(30);
        }

        if (status == "Active")
        {
          active = true;
        }
        else
        {
          active = false;
        }

        for (int tc = 1; tc < columncount; tc++)
        {
          if (tc == 3 || tc == 4 || tc == 6 || tc == 8 || tc == 16 || tc == 17 || tc == 18 || tc == 19 || tc == 20 || tc == 21 || tc == 29 || tc == 30 ||
              tc == 43 || tc == 44 || tc == 47 || tc == 48 || tc == 49 || tc == 50 || tc == 51 || tc == 52 || tc == 53 || tc == 54 || tc == 55 || tc == 56 || tc == 61 ||
              tc == 62 || tc == 63 || tc == 64 || tc == 65 || tc == 66 || tc == 67 || tc == 68 || tc == 69 || tc == 70 || tc == 71 || tc == 89 || tc == 91 || tc == 92 ||
              tc == 94 || tc == 95 || tc == 96 || tc == 97 || tc == 98 || tc == 110 || tc == 163 || tc == 164 || tc == 165 || tc == 166)
          {
            xlc++;
            tcell = row[tc].ToString();

            bool test = false;
            if (row["go_prj"].ToString() != " ")
            {
              test = complete < today;
            }

            if (status == "Active" && pyp >= year)
            {
              if (!String.IsNullOrEmpty(row[tc].ToString()))
              {
                if (tc > 42 && tc < 49)
                {
                  wstc.Cells[lastrowt + 1, xlc + 2] = tcell;
                }
                else if (tc == 49 || tc == 50)
                {
                  wstc.Cells[lastrowt + 1, xlc - 4] = tcell;
                }
                else
                {
                  wstc.Cells[lastrowt + 1, xlc] = tcell;
                }
              }
              
            }

            else
            {
              if (test || status != "Active")
                //if ((test || status != "Active") && open == 0)
              {

                if (!String.IsNullOrEmpty(row[tc].ToString()))
                {
                  if (tc > 42 && tc < 49)
                  {
                    wsrc.Cells[lastrowr + 1, xlc + 2] = tcell;
                  }
                  else if (tc == 49 || tc == 50)
                  {
                    wsrc.Cells[lastrowr + 1, xlc - 4] = tcell;
                  }
                  else
                  {
                    wsrc.Cells[lastrowr + 1, xlc] = tcell;
                  }
                }
              }
              else
              {
                if (!String.IsNullOrEmpty(row[tc].ToString()))
                {
                  if (tc > 42 && tc < 49)
                  {
                    wstc.Cells[lastrowt + 1, xlc + 2] = tcell;
                  }
                  else if (tc == 49 || tc == 50)
                  {
                    wstc.Cells[lastrowt + 1, xlc - 4] = tcell;
                  }
                  else
                  {
                    wstc.Cells[lastrowt + 1, xlc] = tcell;
                  }
                }
              }
            }
          }
        }
      }
    }

    private void formatwstc()
    {
      wstc.Application.ActiveWindow.SplitRow = 1;
      wstc.Application.ActiveWindow.SplitColumn = 6;
      wstc.Application.ActiveWindow.FreezePanes = true;
      lastrowt = wstc.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Row;
      lastcolt = wstc.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Column;
      Excel.Range rng = wstc.Application.get_Range("A1", "AU" + lastrowt);
      rng.Font.Name = "Tahoma";
      rng.Font.Size = 8;
      rng.BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Type.Missing, Type.Missing);
      rng.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
      rng.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
      rng.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin;
      rng.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin;
      Excel.Range header = wstc.Application.get_Range("A1", "AU1");
      header.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
      header.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlMedium;
      Excel.Range dates = wstc.Range["K2:AI" + lastrowt];
      dates.NumberFormat = "mm/dd/yy";
      dates.ColumnWidth = 7.14;
      Excel.Range center = wstc.Range["A:B,D:AI,AT:AU"];
      center.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
      Excel.Range left = wstc.Range["C:C,AJ:AS"];
      left.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
      Excel.Range str = wstc.Range["A:A"];
      str.ColumnWidth = 5;
      Excel.Range seq_strtyp_devtyp = wstc.Range["B:B,E:E,G:G"];
      seq_strtyp_devtyp.ColumnWidth = 3.43;
      Excel.Range cty = wstc.Range["C:C"];
      cty.ColumnWidth = 20.14;
      Excel.Range st = wstc.Range["D:D"];
      st.ColumnWidth = 2.43;
      Excel.Range prjtyp = wstc.Range["F:F"];
      prjtyp.ColumnWidth = 6.43;
      Excel.Range proto = wstc.Range["H:H"];
      proto.ColumnWidth = 4.57;
      Excel.Range sap = wstc.Range["I:I"];
      sap.ColumnWidth = 14.29;
      Excel.Range py = wstc.Range["J:J"];
      py.ColumnWidth = 4.14;
      Excel.Range names = wstc.Range["AJ:AQ,AS:AS"];
      names.ColumnWidth = 14;
      Excel.Range last2 = wstc.Range["AR:AU"];
      last2.ColumnWidth = 7;
      Excel.Range datecells = wstc.Range["K:AI"];
      datecells.ColumnWidth = 7.14;
      Excel.Range centeragain = wstc.Range["A:B,D:AI,AR:AU"];
      centeragain.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
      Excel.Range rows = wstc.Range["2:" + lastrowt];
      rows.RowHeight = 12.75;
      header.Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlMedium;
      header.Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlMedium;
      header.Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlMedium;
      header.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlMedium;
      header.WrapText = true;
      header.Font.Bold = true;
      header.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
      header.RowHeight = 32.25;
      wstc.PageSetup.PrintTitleRows = "$A$1:$AS$1";
      Marshal.FinalReleaseComObject(rng);
      Marshal.FinalReleaseComObject(header);
      Marshal.FinalReleaseComObject(dates);
      Marshal.FinalReleaseComObject(center);
      Marshal.FinalReleaseComObject(left);
      Marshal.FinalReleaseComObject(str);
      Marshal.FinalReleaseComObject(seq_strtyp_devtyp);
      Marshal.FinalReleaseComObject(cty);
      Marshal.FinalReleaseComObject(st);
      Marshal.FinalReleaseComObject(prjtyp);
      Marshal.FinalReleaseComObject(proto);
      Marshal.FinalReleaseComObject(sap);
      Marshal.FinalReleaseComObject(py);
      Marshal.FinalReleaseComObject(names);
      Marshal.FinalReleaseComObject(last2);
      Marshal.FinalReleaseComObject(datecells);
      Marshal.FinalReleaseComObject(centeragain);
      Marshal.FinalReleaseComObject(rows);
    }

    private void formatwsrc()
    {
      wsrc.Application.ActiveWindow.SplitRow = 1;
      wsrc.Application.ActiveWindow.SplitColumn = 6;
      wsrc.Application.ActiveWindow.FreezePanes = true;
      lastrowt = wsrc.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Row;
      lastcolt = wsrc.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Column;
      Excel.Range rng = wsrc.Range["A1:AU" + lastrowt];
      rng.Font.Name = "Tahoma";
      rng.Font.Size = 8;
      rng.BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Type.Missing, Type.Missing);
      rng.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
      rng.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
      rng.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin;
      rng.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin;
      Excel.Range header = wstc.Application.get_Range("A1", "AU1");
      header.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
      header.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlMedium;
      Excel.Range dates = wsrc.Range["K2:AI" + lastrowt];
      dates.NumberFormat = "mm/dd/yy";
      dates.ColumnWidth = 7.14;
      Excel.Range center = wsrc.Range["A:B,D:AI,AT:AU"];
      center.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
      Excel.Range left = wsrc.Range["C:C,AJ:AS"];
      left.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
      Excel.Range str = wsrc.Range["A:A"];
      str.ColumnWidth = 5;
      Excel.Range seq_strtyp_devtyp = wsrc.Range["B:B,E:E,G:G"];
      seq_strtyp_devtyp.ColumnWidth = 3.43;
      Excel.Range cty = wsrc.Range["C:C"];
      cty.ColumnWidth = 20.14;
      Excel.Range st = wsrc.Range["D:D"];
      st.ColumnWidth = 2.43;
      Excel.Range prjtyp = wsrc.Range["F:F"];
      prjtyp.ColumnWidth = 6.43;
      Excel.Range proto = wsrc.Range["H:H"];
      proto.ColumnWidth = 4.57;
      Excel.Range sap = wsrc.Range["I:I"];
      sap.ColumnWidth = 14.29;
      Excel.Range py = wsrc.Range["J:J"];
      py.ColumnWidth = 4.14;
      Excel.Range names = wsrc.Range["AJ:AQ,AS:AS"];
      names.ColumnWidth = 14;
      Excel.Range last2 = wsrc.Range["AR:AU"];
      last2.ColumnWidth = 7;
      Excel.Range datecells = wsrc.Range["K:AI"];
      datecells.ColumnWidth = 7.14;
      Excel.Range centeragain = wsrc.Range["A:B,D:AI,AR:AU"];
      centeragain.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
      Excel.Range rows = wsrc.Range["2:" + lastrowt];
      rows.RowHeight = 12.75;
      header.Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlMedium;
      header.Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlMedium;
      header.Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlMedium;
      header.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlMedium;
      header.WrapText = true;
      header.Font.Bold = true;
      header.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
      header.RowHeight = 32.25;
      wstc.PageSetup.PrintTitleRows = "$A$1:$AS$1";
      Marshal.FinalReleaseComObject(rng);
      Marshal.FinalReleaseComObject(header);
      Marshal.FinalReleaseComObject(dates);
      Marshal.FinalReleaseComObject(center);
      Marshal.FinalReleaseComObject(left);
      Marshal.FinalReleaseComObject(str);
      Marshal.FinalReleaseComObject(seq_strtyp_devtyp);
      Marshal.FinalReleaseComObject(cty);
      Marshal.FinalReleaseComObject(st);
      Marshal.FinalReleaseComObject(prjtyp);
      Marshal.FinalReleaseComObject(proto);
      Marshal.FinalReleaseComObject(sap);
      Marshal.FinalReleaseComObject(py);
      Marshal.FinalReleaseComObject(names);
      Marshal.FinalReleaseComObject(last2);
      Marshal.FinalReleaseComObject(datecells);
      Marshal.FinalReleaseComObject(centeragain);
      Marshal.FinalReleaseComObject(rows);
    }

    private void printerwstc()
    {
      wstc.PageSetup.PrintArea = "";
      wstc.PageSetup.CenterHeader = "";
      wstc.PageSetup.CenterHeader = "&\"Tahoma,Bold\"& Trident Report dated " + wstc.Name;
      wstc.PageSetup.LeftMargin = xlst.InchesToPoints(0.1);
      wstc.PageSetup.RightMargin = xlst.InchesToPoints(0.1);
      wstc.PageSetup.TopMargin = xlst.InchesToPoints(0.65);
      wstc.PageSetup.BottomMargin = xlst.InchesToPoints(0.29);
      wstc.PageSetup.HeaderMargin = xlst.InchesToPoints(0.36);
      wstc.PageSetup.FooterMargin = xlst.InchesToPoints(0.25);
      wstc.PageSetup.PrintTitleRows = "$1:$1";
      wstc.PageSetup.PrintQuality = 600;
      wstc.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;
      wstc.PageSetup.PaperSize = Excel.XlPaperSize.xlPaper11x17;
      wstc.PageSetup.Zoom = 53;
    }

    private void printwsrc()
    {
      wsrc.PageSetup.PrintArea = "";
      wsrc.PageSetup.CenterHeader = "";
      wsrc.PageSetup.CenterHeader = "&\"Tahoma,Bold\"& Trident Report dated " + wstc.Name;
      wsrc.PageSetup.LeftMargin = xlst.InchesToPoints(0.1);
      wsrc.PageSetup.RightMargin = xlst.InchesToPoints(0.1);
      wsrc.PageSetup.TopMargin = xlst.InchesToPoints(0.65);
      wsrc.PageSetup.BottomMargin = xlst.InchesToPoints(0.29);
      wsrc.PageSetup.HeaderMargin = xlst.InchesToPoints(0.36);
      wsrc.PageSetup.FooterMargin = xlst.InchesToPoints(0.25);
      wsrc.PageSetup.PrintTitleRows = "$1:$1";
      wsrc.PageSetup.PrintQuality = 600;
      wsrc.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;
      wsrc.PageSetup.PaperSize = Excel.XlPaperSize.xlPaper11x17;
      wsrc.PageSetup.Zoom = 53;
    }

    private void compare_wstc_to_wstp()
    {
      int lastrowtc = wstc.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Row;
      int lastrowtp = wstp.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Row;
      int lastcoltc = wstc.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Column;
      int lastcoltp = wstp.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Column;
      int crow = 2;

      newstores = new DataTable();
      newstores.Columns.Add("strnum");
      newstores.Columns.Add("strseq");
      newstores.Columns.Add("city");
      newstores.Columns.Add("st");
      newstores.Columns.Add("strtyp");
      newstores.Columns.Add("prjtyp");


      for (int rc = 2; rc < lastrowtc; rc++)
      {
        Excel.Range strce = wstc.Cells[rc, 1];
        Excel.Range seqce = wstc.Cells[rc, 2];
        Excel.Range cityce = wstc.Cells[rc, 3];
        Excel.Range stce = wstc.Cells[rc, 4];
        Excel.Range strtypce = wstc.Cells[rc, 5];
        Excel.Range prjtypce = wstc.Cells[rc, 6];
        string strc = Convert.ToString(strce.Value);
        string seqc = Convert.ToString(seqce.Value);
        string cityc = Convert.ToString(cityce.Value);
        string stc = Convert.ToString(stce.Value);
        string strtypc = Convert.ToString(strtypce.Value);
        string prjtypc = Convert.ToString(prjtypce.Value);
        string strseqc = strc + seqc;
        bool found = false;

        for (int rp = 2; rp < lastrowtp; rp++)
        {
          Excel.Range strpe = wstp.Cells[rp, 1];
          Excel.Range seqpe = wstp.Cells[rp, 2];
          string strp = Convert.ToString(strpe.Value);
          string seqp = Convert.ToString(seqpe.Value);
          string strseqp = strp + seqp;

          if (strseqp == strseqc)
          {
            crow = rp;

            for (int cc = 3; cc < lastcoltc; cc++)
            {
              Excel.Range currente = wstc.Cells[rc, cc];
              Excel.Range previouse = wstp.Cells[rp, cc];
              string current = Convert.ToString(currente.Value);
              string previous = Convert.ToString(previouse.Value);

              //this is only for the 11/1/12 transfer
              if (previous == null)
              {
                previous = " ";
              }

              if (current == null)
              {
                current = " ";
              }
              //down to here

              if (current == previous)
              {
                continue;
              }
              else
              {
                Excel.Range firstcol = wstc.Cells[rc, 1];
                currente = wstc.Cells[rc, cc];
                currente.Interior.Color = Color.Goldenrod;
                firstcol.Interior.Color = Color.Goldenrod;
              }
              Marshal.FinalReleaseComObject(currente);
              Marshal.FinalReleaseComObject(previouse);
            }

            found = true;
            Marshal.FinalReleaseComObject(strce);
            Marshal.FinalReleaseComObject(seqce);
            Marshal.FinalReleaseComObject(cityce);
            Marshal.FinalReleaseComObject(stce);
            Marshal.FinalReleaseComObject(strtypce);
            Marshal.FinalReleaseComObject(prjtypce);
            Marshal.FinalReleaseComObject(strpe);
            Marshal.FinalReleaseComObject(seqpe);
            break;
          }

          else if (rp == lastrowtp - 1 && found == false)
          {
            Excel.Range colorrow = wstc.Range["A" + rc + ":" + "AU" + rc];
            colorrow.Interior.Color = Color.Aquamarine;
            object[] addedstore = new object[6];

            addedstore[0] = strc;
            addedstore[1] = seqc;
            addedstore[2] = cityc;
            addedstore[3] = stc;
            addedstore[4] = strtypc;
            addedstore[5] = prjtypc;
            newstores.Rows.Add(addedstore);
            Marshal.FinalReleaseComObject(colorrow);
          }
        }
      }
    }

    private void compare_wsrc_to_wsrp()
    {
      int lastrowtc = wsrc.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Row;
      int lastrowtp = wsrp.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Row;
      int lastcoltc = wsrc.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Column;
      int lastcoltp = wsrp.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Column;
      bool found = false;
      int crow = 2;


      for (int rc = 2; rc < lastrowtc; rc++)
      {
        Excel.Range strce = wsrc.Cells[rc, 1];
        Excel.Range seqce = wsrc.Cells[rc, 2];
        string strc = Convert.ToString(strce.Value);
        string seqc = Convert.ToString(seqce.Value);
        string strseqc = strc + seqc;

        for (int rp = crow; rp < lastrowtp; rp++)
        {
          Excel.Range strpe = wsrp.Cells[rp, 1];
          Excel.Range seqpe = wsrp.Cells[rp, 2];
          string strp = Convert.ToString(strpe.Value);
          string seqp = Convert.ToString(seqpe.Value);
          string strseqp = strp + seqp;

          if (strseqp == strseqc)
          {
            crow = rp;

            for (int cc = 3; cc < lastcoltc; cc++)
            {
              Excel.Range currente = wsrc.Cells[rc, cc];
              Excel.Range previouse = wsrp.Cells[rp, cc];
              string current = Convert.ToString(currente.Value);
              string previous = Convert.ToString(previouse.Value);

              //this is only for the 11/1/12 transfer
              if (previous == null)
              {
                previous = " ";
              }

              if (current == null)
              {
                current = " ";
              }
              //down to here

              if (current == previous)
              {
                continue;
              }
              else
              {
                Excel.Range firstcol = wsrc.Cells[rc, 1];
                currente = wsrc.Cells[rc, cc];
                currente.Interior.Color = Color.Goldenrod;
                firstcol.Interior.Color = Color.Goldenrod;
                Marshal.FinalReleaseComObject(firstcol);
              }

              Marshal.FinalReleaseComObject(currente);
              Marshal.FinalReleaseComObject(previouse);
            }

            found = true;
            Marshal.FinalReleaseComObject(strce);
            Marshal.FinalReleaseComObject(seqce);
            Marshal.FinalReleaseComObject(strpe);
            Marshal.FinalReleaseComObject(seqpe);
            break;
          }

          else if (rp == lastrowtp - 1 && found == false)
          {
            Excel.Range colorrow = wsrc.Range["A" + rc + ":" + "AU" + rc];
            colorrow.Interior.Color = Color.Aquamarine;
          }
        }
      }
    }

    private void pastdue()
    {
      int lastrowtc = wstc.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Row;

      for (int rc = 2; rc < lastrowtc; rc++)
      {
        Excel.Range citye = wstc.Cells[rc, 3];
        string city = citye.Value;
        Excel.Range pwoidprje = wstc.Cells[rc, 17];
        Excel.Range pwoidacte = wstc.Cells[rc, 18];
        Excel.Range pwoprje = wstc.Cells[rc, 19];
        Excel.Range pwoacte = wstc.Cells[rc, 20];
        Excel.Range otpprje = wstc.Cells[rc, 21];
        Excel.Range otpacte = wstc.Cells[rc, 22];
        Excel.Range permitprje = wstc.Cells[rc, 23];
        Excel.Range permitacte = wstc.Cells[rc, 24];
        string pwip = Convert.ToString(pwoidprje.Value);
        string pwia = Convert.ToString(pwoidacte.Value);
        string pwp = Convert.ToString(pwoprje.Value);
        string pwa = Convert.ToString(pwoacte.Value);
        string opp = Convert.ToString(otpprje.Value);
        string opa = Convert.ToString(otpacte.Value);
        string pp = Convert.ToString(permitprje.Value);
        string pa = Convert.ToString(permitacte.Value);
        DateTime pwoidprj = new DateTime();
        DateTime pwoprj = new DateTime();
        DateTime otpprj = new DateTime();
        DateTime permitprj = new DateTime();

        if (pwip != " ")
        {
          pwoidprj = Convert.ToDateTime(pwoidprje.Value);
        }

        if (pwp != " ")
        {
          pwoprj = Convert.ToDateTime(pwoprje.Value);
        }

        if (opp != " ")
        {
          otpprj = Convert.ToDateTime(otpprje.Value);
        }

        if (pp != " ")
        {
          permitprj = Convert.ToDateTime(permitprje.Value);
        }

        if (today > pwoidprj && pwia == " ")
        {
          pwoidacte.Interior.Color = Color.Red;
        }

        if (today > pwoprj && pwa == " ")
        {
          pwoacte.Interior.Color = Color.Red;
        }

        if (today > otpprj && opa == " ")
        {
          otpacte.Interior.Color = Color.Red;
        }

        if (today > permitprj && pa == " ")
        {
          permitacte.Interior.Color = Color.Red;
        }

        Marshal.FinalReleaseComObject(citye);
        Marshal.FinalReleaseComObject(pwoidprje);
        Marshal.FinalReleaseComObject(pwoidacte);
        Marshal.FinalReleaseComObject(pwoprje);
        Marshal.FinalReleaseComObject(pwoacte);
        Marshal.FinalReleaseComObject(otpprje);
        Marshal.FinalReleaseComObject(otpacte);
        Marshal.FinalReleaseComObject(permitprje);
        Marshal.FinalReleaseComObject(permitacte);
      }
    }

    
    private string PreambleInjection ()
    {
      return @"<p>In an effort to centralize information and provide you with more up-to-date information, Doug and I created a web based tool.  You may already be using it and we would appreciate feedback.
      Follow this link to your 
<a style=""color:#ba0d0d;font-weight:bold;text-decoration:none;"" href=""http://winxp134b.rha.local/trail/projectdates/aggregated""><strong>Daily Trident</strong></a>
 Report.</p>";

    }
    
    private void message()
    {
      string preamble = "";
      string postscript = "</div></body></html>";
      string trident = @"<a style=""color:#ba0d0d;font-weight:bold;text-decoration:none;"" href=""file://///w2008svr5/u/Reports/Trident/Trident.xls""><strong>Trident</strong></a>";
      string removed = @"<a style=""color:#ba0d0d;font-weight:bold;text-decoration:none;"" href=""file://///w2008svr5/u/Reports/Trident/Removed_Stores.xls""><strong>Removed Stores</strong></a>";
      string table = @"
<TABLE style=""background:#e7e7e7;color:#312e25;font-size:1.25em"" cols=""1"" cellspacing=""1"" cellpadding=""0"">";

      preamble = @"
<html lang=""us"">
  <body style=""width:440px;margin:10px;padding:10px;font-family:helvetica,tahoma,arial,sans-serif;"">
    <div style=""width:440px;font-size:62.5%;margin:10px;padding:10px;font-family:helvetica,tahoma,arial,sans-serif;"">
    
    " + PreambleInjection () + 
    
      @"<div style=""width:440px;border-top-left-radius:6px;border-top-right-radius:6px;border-bottom-left-radius:6px;border-bottom-right-radius:6px;background:#e7e7e7;color:#312e25;font-size:1.25em"">
        The excel file is updated irregulary.  Here is the link to the excel file:<br />" + trident + "<br />" +
        "Here is the link for Removed Stores:<br />" + removed + @"<br />
         Here are the added stores for this week: <br />";
      //postscript = "There are several added stores because the open deviations are back...so some stores that had open deviations were added back.<br />"; //remember to remove this line...
      for (int i = 0; i < newstores.Rows.Count; i++)
      {
        if (newstores.Rows.Count != 0)
        {
          DataRow addedstore;
          addedstore = newstores.Rows[i];
          table += String.Format(@" <TR>
                        <TD valign=""top"" align=""left"">
                            <DIV>
                            <NOBR>{0}</NOBR>
                            </DIV>
                        </TD>
                        
                        </TR>", addedstore["strnum"] + "-" + addedstore["strseq"] + "  " + addedstore["city"] + ", " + addedstore["st"] + "   " +
                                 addedstore["strtyp"] + "   " + addedstore["prjtyp"]);
        }

        else
        {
          table += String.Format(@" <TR>
                        <TD valign=""top"" align=""left"">
                            <DIV>
                            <NOBR>{0}</NOBR>
                            </DIV>
                        </TD>
                        
                        </TR>", "No added stores this week.");
        }

      }
      table += "</TABLE>";
      string format = "MMMM d, yyyy";
      Trident.Mail.mailman.sendmail("Trident Report dated " + today.ToString(format), preamble + table + postscript);
    }

    private void shutdown()
    {
      Excel.Range ta1 = wstc.Range["A1"];
      ta1.Select();
      Marshal.FinalReleaseComObject(ta1);
      Excel.Range ra1 = wsrc.Range["A1"];
      ra1.Select();
      Marshal.FinalReleaseComObject(ra1);
      wbr.Save();
      wbt.Save();
      wbr.Close();
      wbt.Close();
      xlst.Application.Quit();
      xlsr.Application.Quit();
      xlst.Quit();
      xlsr.Quit();
      Marshal.FinalReleaseComObject(xlst);
      Marshal.FinalReleaseComObject(xlsr);
      Marshal.FinalReleaseComObject(wbt);
      Marshal.FinalReleaseComObject(wstc);
      Marshal.FinalReleaseComObject(wstp);
      Marshal.FinalReleaseComObject(wbr);
      Marshal.FinalReleaseComObject(wsrc);
      Marshal.FinalReleaseComObject(wsrp);
      Marshal.FinalReleaseComObject(wstc);
      Marshal.FinalReleaseComObject(wstp);
      Marshal.FinalReleaseComObject(wsrc);
      Marshal.FinalReleaseComObject(wsrp);
      Marshal.FinalReleaseComObject(wbt);
      Marshal.FinalReleaseComObject(wbr);
      Marshal.FinalReleaseComObject(xlst);
      Marshal.FinalReleaseComObject(xlsr);
      tridenttable = null;
      connect = null;
      row = null;
      bldgportalpage = null;
      newstores = null;
      GC.Collect();
      GC.WaitForPendingFinalizers();
      GC.Collect();
      GC.WaitForPendingFinalizers();
      Application.Exit();
      
    }
  }
}
